package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sun.jndi.url.dns.dnsURLContext;
import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.NewsComment;
import com.wanmait.hotelManage.vo.NewsCommentReport;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Userinfo;

public class NewsCommentDAO {
	private NewsCommentDAO() {};
	private static NewsCommentDAO newsCommentDAO=new NewsCommentDAO();
	public static NewsCommentDAO getNewsCommentDAO() {
		return newsCommentDAO;
	}
	/**
	 * 新闻举报的全部查找
	 * @param pager 
	 * @return
	 */
	public List<NewsComment> findAll(Pager pager){
		List<NewsComment>newsComments=new ArrayList<NewsComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT news_comment.content,news_comment.id,news_comment.userinfo_id,userinfo.username,userinfo.id ");
		sb.append(" FROM news_comment,userinfo,news_comment_report ");
		sb.append(" WHERE userinfo.id=news_comment.userinfo_id AND news_comment_report.news_comment_id=news_comment.id AND news_comment.enable=1 AND news_comment_report.read=0 order by news_comment_report.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			NewsComment nc=null;
			Userinfo ui=null;
			while(rs.next()) {
				nc=new NewsComment();
				nc.setId(rs.getInt("news_comment.id"));
				nc.setContent(rs.getString("news_comment.content"));
				ui=new Userinfo();
				ui.setId(rs.getInt("userinfo.id"));
				ui.setUsername(rs.getString("userinfo.username"));
				nc.setUserinfo(ui);
				newsComments.add(nc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return newsComments;
	}
//	public static void main(String[] args) {
//		List<NewsComment>nn=NewsCommentDAO.getNewsCommentDAO().findAll();
//		for (NewsComment newsComment : nn) {
//			System.out.println(newsComment.getUserinfo().getUsername());
//		}
//	}
	/**
	 * 新闻评论举报成立
	 */
	public void up(Integer id) {
		String sql="update news_comment set enable=0 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	/**
	 * 新闻举报批量通过的值
	 * @param ids
	 */
	public void ups(String ids) {
		String sql="update news_comment set enable=0 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	public List<NewsComment> findid1(String id1, Pager pager) {
		List<NewsComment>newsComments=new ArrayList<NewsComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT news_comment.content,news_comment.id,news_comment.userinfo_id,userinfo.username,userinfo.id ");
		sb.append(" FROM news_comment,userinfo,news_comment_report ");
		sb.append(" WHERE userinfo.id=news_comment.userinfo_id  AND news_comment_report.userinfo_id IN ("+id1+") AND news_comment_report.news_comment_id=news_comment.id AND news_comment.enable=1 AND news_comment_report.read=0 order by news_comment_report.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			NewsComment nc=null;
			Userinfo ui=null;
			while(rs.next()) {
				nc=new NewsComment();
				nc.setId(rs.getInt("news_comment.id"));
				nc.setContent(rs.getString("news_comment.content"));
				ui=new Userinfo();
				ui.setId(rs.getInt("userinfo.id"));
				ui.setUsername(rs.getString("userinfo.username"));
				nc.setUserinfo(ui);
				newsComments.add(nc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return newsComments;
	}
	public ArrayList<NewsComment> getFirstComment(Integer id)
	{
		ArrayList<NewsComment> allComments = new ArrayList<NewsComment>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM news_comment,userinfo WHERE ");
		sb.append(" news_comment.userinfo_id=userinfo.id and news_comment.enable=1 and parent_id=0 and news_id=?");
		String sql = sb.toString();
		
		try {
			conn= DBHelper.getDBHelper().getConn();
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			
			while(rs.next())
			{
				Userinfo userinfo = new Userinfo();;
				userinfo.setId(rs.getInt("userinfo.id"));
				userinfo.setUsername(rs.getString("userinfo.username"));
				
				NewsComment comment = new NewsComment();
				comment.setContent(rs.getString("content"));
				comment.setId(rs.getInt("news_comment.id"));
				comment.setInputTime(rs.getTimestamp("news_comment.input_time"));
				comment.setUserinfo(userinfo);
				
				//查询第一级评论的children
				this.getChildren(comment);
				
				allComments.add(comment);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return allComments;//返回的是第一级评论
	}
	/**
	 * 获得子评论
	 * @param parent
	 */
	public void getChildren(NewsComment parent)
	{
		ArrayList<NewsComment> children = new ArrayList<NewsComment>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM news_comment,userinfo WHERE ");
		sb.append(" news_comment.userinfo_id=userinfo.id and parent_id=?  and news_comment.enable=1");
		String sql = sb.toString();
		try {
			conn = DBHelper.getDBHelper().getConn();
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, parent.getId());
			
			rs = ps.executeQuery();
			
			while(rs.next())
			{
				Userinfo userinfo = new Userinfo();
				userinfo.setId(rs.getInt("userinfo.id"));
				userinfo.setUsername(rs.getString("userinfo.username"));
				
				NewsComment comment = new NewsComment();				
				comment.setContent(rs.getString("content"));
				comment.setId(rs.getInt("news_comment.id"));
				comment.setInputTime(rs.getTimestamp("news_comment.input_time"));
				comment.setUserinfo(userinfo);
				
				children.add(comment);
				
				comment.setNewsComment(parent);
				
				this.getChildren(comment);//获得comment评论的所有的子评论
			}
			
			parent.setChildren(children);
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
	}
	public Integer getrows(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql="SELECT count(*) c FROM news_comment where news_id=? and enable=1";
		conn = DBHelper.getDBHelper().getConn();
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			
			rs = ps.executeQuery();
			if(rs.next()) {
				int rows=rs.getInt("c");
				return rows;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}
	/**
	 * 举报新闻评论
	 * @param id
	 * @param id2
	 * @param a 
	 */
	public void insert(Integer id, Integer id2, String a) {
		String sql="insert into news_comment_report(userinfo_id,news_comment_id,reason)values(?,?,?)";
		DBHelper.getDBHelper().executeUpdate(sql, id2,id,a);
	}
	/**
	 * 添加新闻评论
	 * @param textare
	 * @param id
	 * @param id2
	 * @param texti
	 */
	public void insertComment(String textare, Integer id, Integer id2, Integer texti) {
		String sql="insert into news_comment(content,userinfo_id,news_id,parent_id)values(?,?,?,?)";
		DBHelper.getDBHelper().executeUpdate(sql, textare,id,id2,texti);
	}
}
